/**
 * taskmaster.OracleConnect
 *
 * Generic conenction to oracle db, returns a db connection to user when instantiated + logged in
 *
 */
package cgi.db;

import java.sql.*;

public class OracleConnect
{

    public static final String DEFAULT_DBNAME = "dev";        // DEFAULT: set db to dev if not told otherwise
    public static final String DEFAULT_HOST = "oci8";        // DEFAULT: set host to cgis08 (oci8) not told otherwise

    /**
     * A login method to access the db
     * Returns a connection to the db for this user/pass combo, null if error
     * assumes host of DEFAULT_HOST and dbname DEFAULT_DBNAME
     */
    public static Connection getConnection(String username, String password) throws Exception
    {
        return connect(username, password, DEFAULT_HOST, DEFAULT_DBNAME);
    }

    public static Connection getConnectionOps(String username, String password) throws Exception
    {
        if (username != null && !username.startsWith("ops$"))
        {
            username = "ops$" + username;
        }
        return connect(username, password, DEFAULT_HOST, DEFAULT_DBNAME);
    }

    public static Connection getConnection(String username, String password, String dbname) throws Exception
    {
        return connect(username, password, DEFAULT_HOST, dbname);
    }

    public static Connection getConnectionOps(String username, String password, String dbname) throws Exception
    {
        if (username != null && !username.startsWith("ops$"))
        {
            username = "ops$" + username;
        }
        return connect(username, password, DEFAULT_HOST, dbname);
    }

    /**
     * A login method to access the db
     * Returns a connection to the db for this user/pass combo, null if error
     * Sets host to given parameters (must all be non-null)
     */
    public static Connection getConnection(String username, String password, String host, String dbname) throws Exception
    {
        try
        {
            return connect(username, password, host, dbname);
        } catch (Exception e)
        {
            String expl = "taskmaster.util.OracleConnect.getConnection: Can't connect to db for user='" + username + "', with host='" + host + "', dbname='" + dbname + "' " + e;
            System.err.println(expl);
            throw new Exception(expl, e);
        }
    }

    public static Connection getConnectionOps(String username, String password, String host, String dbname) throws Exception
    {
        try
        {
            if (!username.equalsIgnoreCase("") && !password.equalsIgnoreCase("")/*&& !username.startsWith("ops$")*/)
            {
                username = "ops$" + username;
            }
            return connect(username, password, host, dbname);
        } catch (Exception e)
        {
            String expl = "taskmaster.util.OracleConnect.getConnection: Can't connect to db for user='" + username + "', with host='" + host + "', dbname='" + dbname + "' " + e;
            System.err.println(expl);
            throw new Exception(expl, e);
        }
    }


    private static Connection connect(String username, String password, String host, String dbname) throws Exception
    {
        if (username == null || password == null)
        {
            throw new Exception("Username or password was null");
        }
        System.out.println("DBConnect:");
        System.out.println("Using username: " + username);
        System.out.println("Using host:     " + host);
        System.out.println("Using dbname:   " + dbname);

        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

        System.setProperty("db.host", host);
        System.setProperty("db.dbname", dbname);
        final String dbUrl = "jdbc:oracle:oci8:@" + dbname;
        System.out.println("Using dbUrl:    " + dbUrl);
        Connection con = DriverManager.getConnection(dbUrl, username, password);

        // set auto commit off so we have to commit()/rollback() explicitly
        con.setAutoCommit(false);
        // this is necessary in case default date format is DD/MM or something.
        con.createStatement().executeUpdate("alter session set nls_date_format='MM/DD/YYYY'");

        System.out.println("UserFromCon:    " + getUserNameFromConnection(con));

        return con;
    }

    public static String getUserNameFromConnection(Connection con) throws SQLException
    {
        Statement name_stmt = con.createStatement();
        ResultSet name_rset = name_stmt.executeQuery("select username from user_users");
        String userName = "";
        while (name_rset.next())
        {
            userName = name_rset.getString(1);
        }
        name_stmt.close();
        name_rset.close();
        // strip ops$ from userName
        return userName.substring(4, userName.length()).toLowerCase();
    }

    /**
     * Simple test of the oracle connection through the command line.  Prints only message if successful, error otherwise
     */
    public static void main(String[] args)
    {
        try
        {
            OracleConnect o = new OracleConnect();
            o.getConnection(args[0], args[1]);

            System.out.println("If this is the only output that you see, I've connected to the db success-fully.");
        } catch (Exception e)
        {
            System.err.println("Exception in OracleConnect:Main: " + e);
            e.printStackTrace();
        }
    }
}
